package cn.edu.zzuli.web.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import cn.edu.zzuli.web.bean.Forum;
import cn.edu.zzuli.web.bean.rowmapper.ForumRowMapper;
import cn.edu.zzuli.web.dao.base.AbstractDao;

@Repository("forumDAO")
public class ForumDAO extends AbstractDao {
	
	private SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd HH:mm");

	public ForumDAO() {
	}

	public int getTotalForum() {
		StringBuffer sql = new StringBuffer("select count(1) from Forum where pid = 0");
		return this.getJdbcTemplate().queryForInt(sql.toString());
	}
	
	public int getTotalListForum(int pid) {
		StringBuffer sql = new StringBuffer("select count(1) from Forum where pid = ?");
		return this.getJdbcTemplate().queryForInt(sql.toString(),new Object[] { pid });
	}

	public List<Forum> getForumByPage(int pageSize, int currentPage) {
		String s = "select id,pid,title,author,content,sendTime,replyTime,lastReplyMenber,views,replys,bak "
				+ "from Forum where pid = 0 limit ?,?";
		StringBuffer sql = new StringBuffer(s);
		return this.getJdbcTemplate().query(sql.toString(),
				new Object[] { pageSize * (currentPage - 1), pageSize },
				new ForumRowMapper());
	}
	
	public List<Forum> getForumListByPage(int pageSize, int currentPage,int pid) {
		String s = "select id,pid,title,author,content,sendTime,replyTime,lastReplyMenber,views,replys,bak "
				+ "from Forum where pid = ? limit ?,?";
		StringBuffer sql = new StringBuffer(s);
		return this.getJdbcTemplate().query(sql.toString(),
				new Object[] { pid , pageSize * (currentPage - 1), pageSize },
				new ForumRowMapper());
	}

	public List<Forum> getForumByID(List<Integer> list) {
		HashMap<String, List<Integer>> paramMap = new HashMap<String, List<Integer>>();
		paramMap.put("list", list);
		String sql = "select id,pid,title,author,content,sendTime,replyTime,lastReplyMenber,views,replys,bak "
				+ "from Forum where id in(:list)";
		return new NamedParameterJdbcTemplate(this.getJdbcTemplate().getDataSource())
				.query(sql, paramMap,new ForumRowMapper());
	}

	public int insert(Forum forum) {
		String sql = "insert into Forum (pid,title,author,content,sendTime,replyTime,lastReplyMenber,views,replys,bak) "
				+ "values(?,?,?,?,?,?,?,?,?,?)";
		Object[] params = new Object[] { forum.getPid(), forum.getTitle(),
				forum.getAuthor(), forum.getContent(), sdf.format(new Date()),
				sdf.format(new Date()), forum.getLastReplyMenber(),
				forum.getViews(), forum.getReplys(), forum.getBak() };
		int[] types = new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR,
				Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.INTEGER, Types.INTEGER, Types.VARCHAR };
		return this.getJdbcTemplate().update(sql, params, types);
	}

	public Forum getForumByRowCallbackHandler(int id) {
		String sql = "select * from Forum where id = ?";
		final Forum forum = new Forum();
		final Object params[] = new Object[] { id };
		this.getJdbcTemplate().query(sql, params, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				forum.setId(rs.getInt("id"));
				forum.setPid(rs.getInt("pid"));
				forum.setTitle(rs.getString("title"));
				forum.setAuthor(rs.getString("author"));
				forum.setContent(rs.getString("content"));
				forum.setViews(rs.getInt("views"));
				forum.setReplys(rs.getInt("replys"));
				forum.setSendTime(rs.getString("sendTime"));
				forum.setReplyTime(rs.getString("replyTime"));
				forum.setLastReplyMenber(rs.getString("lastReplyMenber"));
				forum.setBak(rs.getString("bak"));
			}
		});
		return forum;
	}
	
	public void deleteForum(int id){
		String sql = "update Forum set pid = -1 where id = ?";
		Object[] params = new Object[] { id };
		int[] types = new int[] { Types.INTEGER };
		this.getJdbcTemplate().update(sql, params, types);
	}
	
	public void deleteUnnecessary(){
		String sql = "select id from forum where pid NOT in (SELECT id FROM forum where pid = 0) and pid >0";
		List<Map<String,Object>> list = this.getJdbcTemplate().queryForList(sql);
		for (Map<String,Object> m : list) {
			String usql = "update forum set pid = -1 where id = ?";
			Object[] params = new Object[] { m.get("id") };
			int[] types = new int[] { Types.INTEGER };
			this.getJdbcTemplate().update(usql, params, types);
		}
	}

}
